const Service = require('egg').Service
const town = require('../data/town')
const dayjs = require('dayjs')

// 返乡人员
class HsService extends Service {
  /**
   * 通过乡镇名称获取返乡人员核酸检查数
   * @param {乡镇名称} town
   */
  async hxryHsTotalByTown(townName) {
    // return this.ctx.model.Hs.count({
    //   town,
    //   fxry: true,
    // })
    // return this.ctx.model.Hs.aggregate([
    //   {
    //     $match: {
    //       town,
    //       fxry: true,
    //     },
    //   },
    //   {
    //     $group: {
    //       _id: '$idCard', // 身份证号去重
    //     },
    //   },
    //   {
    //     $group: {
    //       _id: null,
    //       count: {
    //         $sum: 1,
    //       },
    //     },
    //   },
    // ]).then(res => {
    //   return res.length ? res[0].count : 0
    // })

    const { knex } = this.app
    const { TABLE_HS_RESULT, TABLE_PERSON_BACK_HOME } = this.config.constant

    return knex
      .select(knex.raw([`count(*) AS total`].join(',')))
      .from(function () {
        this.select(knex.raw([`count(*) AS total`].join(',')))
          .from(function () {
            this.table(TABLE_HS_RESULT)
              .join(
                TABLE_PERSON_BACK_HOME,
                `${TABLE_HS_RESULT}.yw_id_card`,
                '=',
                `${TABLE_PERSON_BACK_HOME}.id_number`
              )
              .select(`${TABLE_HS_RESULT}.*`, `${TABLE_PERSON_BACK_HOME}.to_county_code`)
              .where(`${TABLE_PERSON_BACK_HOME}.to_county_code`, townName)
              .as('hsResult')
          })
          .groupBy(`hsResult.yw_id_card`)
          .as('hsResultGroupBy')
      })
      .then(res => (res.length ? res[0].total : 0))
  }

  /**
   * 按时间范围获取乡镇核酸结果
   * @param  [scope=day] 范围 day/week/month
   * @param  [date=今天] 日期
   */
  async townHsResultByScope(...args) {
    const { knex } = this.app
    const { TABLE_PERSON, TABLE_HS_RESULT } = this.config.constant
    const { helper } = this.ctx

    const { startTime, endTime } = helper.getDateOptions(...args)

    const data = town.map(item => {
      return {
        label: item,
        total: 0,
        positive: 0,
        negative: 0,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label
      const _this = this

      // 应采人数
      item.ycrs = await knex(TABLE_PERSON)
        .where('areajd', townName)
        .count()
        .then(res => res[0]['count(*)'])

      // 无法去重
      await knex
        .select(
          knex.raw(
            [
              `count( * ) AS total`,
              `count( positive != 0 OR NULL ) AS positive`,
              `count( negative != 0 OR NULL ) AS negative`,
            ].join(',')
          )
        )
        .from(function () {
          this.select(
            knex.raw(
              [
                `count(*) AS total`,
                `count(yw_check_status = '阳性' or null) AS positive`,
                `count(yw_check_status = '阴性' or null) AS negative`,
              ].join(',')
            )
          )
            .from(function () {
              _this.fromUserArea(this, townName)
            })
            .groupBy('hsResult.yw_id_card')
            .as('hsResultGroup')
        })

        .then(res => {
          for (const key in res[0]) {
            item[key] = res[0][key]
          }
        })

      // 未采人数
      item.wcrs = item.ycrs > item.total ? item.ycrs - item.total : 0

      // 应采人数
      item.percen = item.ycrs > 0 ? Number(((item.total / item.ycrs) * 100).toFixed(2)) : 0
    })

    return data
  }

  /**
   * 乡镇核酸结果汇总
   */
  async townHsResult() {
    const { knex } = this.app
    const { TABLE_PERSON, TABLE_HS_RESULT } = this.config.constant
    const { helper } = this.ctx

    const data = town.map(item => {
      return {
        label: item,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label
      const _this = this

      await knex
        .select(knex.raw([`count(*) AS value`].join(',')))
        .from(function () {
          _this.fromUserArea(this, townName)
        })
        .then(res => {
          for (const key in res[0]) {
            item[key] = res[0][key]
          }
        })
    })

    return data
  }

  // 子查询，核酸结果关联用户所在乡镇
  fromUserArea(_this, townName) {
    const { TABLE_PERSON, TABLE_HS_RESULT } = this.config.constant

    _this
      .table(TABLE_HS_RESULT)
      .join(TABLE_PERSON, `${TABLE_HS_RESULT}.yw_id_card`, '=', `${TABLE_PERSON}.cardno`)
      .select(`${TABLE_HS_RESULT}.*`, `${TABLE_PERSON}.areajd`)
      .where('areajd', townName)
      .as('hsResult')
  }

  /**
   * 乡镇核酸采集汇总
   */
  async townHsCollect() {
    const { knex } = this.app
    const { TABLE_HS_COLLECT } = this.config.constant
    const { helper } = this.ctx

    const data = town.map(item => {
      return {
        label: item,
      }
    })

    await helper.queue(data, async (item, index) => {
      const townName = item.label

      await knex(TABLE_HS_COLLECT)
        .select(knex.raw([`count(*) AS value`].join(',')))
        .where('collect_point_town', townName)
        .then(res => {
          for (const key in res[0]) {
            item[key] = res[0][key]
          }
        })
    })

    return data
  }

  /**
   * 获取重点人员核酸数量
   */
  async getZdryHsCount(townName, beforeDay = 3) {
    const { knex } = this.app
    const { TABLE_HS_RESULT, TABLE_ZDRY } = this.config.constant

    return knex
      .select(knex.raw([`count(*) AS total`].join(',')))
      .from(function () {
        this.table(TABLE_HS_RESULT)
          .join(TABLE_ZDRY, `${TABLE_HS_RESULT}.yw_id_card`, '=', `${TABLE_ZDRY}.person_id_card`)
          .select(`${TABLE_HS_RESULT}.*`, `${TABLE_ZDRY}.areajd`)
          .where(
            'YW_REPORT_TIME',
            '>=',
            dayjs()
              .add(0 - beforeDay, 'day')
              .format('YYYY-MM-DD HH:mm:ss')
          )
          .where('YW_REPORT_TIME', '<=', dayjs().format('YYYY-MM-DD HH:mm:ss'))
          .as('hsResult')
          .where('areajd', townName)
      })
      .groupBy(`hsResult.yw_id_card`)
      .then(res => (res.length ? res[0].total : 0))
  }

  /**
   * 获取居家隔混采记录
   */
  async getJjglHcCount(townName) {
    const { knex } = this.app
    const { TABLE_HS_COLLECT, TABLE_JJGL } = this.config.constant

    return knex
      .select(knex.raw([`count(*) AS total`].join(',')))
      .from(function () {
        this.table(TABLE_HS_COLLECT)
          .join(TABLE_JJGL, `${TABLE_HS_COLLECT}.col_card`, '=', `${TABLE_JJGL}.cardno`)
          .select(`${TABLE_HS_COLLECT}.*`, `${TABLE_JJGL}.areajd`, `${TABLE_JJGL}.lktime`)
          // .where(`${TABLE_HS_COLLECT}.collect_point_town`, townName)
          .whereNot(`${TABLE_HS_COLLECT}.col_code_type`, 1) // 非单采
          .where(`${TABLE_JJGL}.areajd`, townName)
          .where(`${TABLE_JJGL}.lktime`, '>=', dayjs().format('YYYY-MM-DD HH:mm:ss')) // 结束隔离时间大于当前 TODO这里要等真实数据验证一下
          .as('hsResult')
      })
      .groupBy(`hsResult.col_card`)
      .then(res => (res.length ? res[0].total : 0))
  }
}

module.exports = HsService
